import pandas as pd
import os
import xlwings as xw
import time
import re
import datetime


class Re_port:
    def __init__(self):
        x = 'D84-BATTERY-AND-VHB-INSTALL-Versions'
        self.path = None
        self.put_name = f"{x}{datetime.date.today()}.xlsx"
        failes = os.listdir('./')
        for i in failes:
            if x in i:
                self.path = i
                print(self.path)
                break

    def hand_data(self):
        try:
            col = ['Station ID', 'Dynamic_Rolling_Time']
            df = pd.read_csv(self.path, header=1, skiprows=[2, 3, 4, 5, 6],
                             usecols=col)

            re_s = '[A-Z0-9]+-[A-Z0-9]+-[A-Z0-9_]+'
            df['Station ID'] = df['Station ID'].apply(lambda x: (re.findall(re_s, x))[0])
            df = df.drop_duplicates(subset=['Station ID'], keep='last')
            df[['Floor', 'Line', 's']] = df['Station ID'].str.split('_', expand=True)
            df['Floor'] = df['Floor'].apply(lambda x: x.split('-')[-1] + '-' + x.split('-')[0] + '-' + x.split('-')[1])
            df = df.reindex(['Floor', 'Line', 'Dynamic_Rolling_Time'], axis=1)
            df = df.rename(columns={"Dynamic_Rolling_Time": 'time'})
            df = df.sort_values(by=['Floor', 'Line'])
            df['Floor'] = df['Floor'].apply(lambda x: x.split('-')[1] + '-' + x.split('-')[-1] + '-' + x.split('-')[0])
            df['Line'] = df['Line'].astype(int)
            df.to_excel(self.put_name, index=False)

            with xw.App(visible=False) as app:
                wb = app.books.open(f'{self.put_name}')
                sht = wb.sheets[0]
                sht.autofit()
                for n, v in enumerate(sht.range('C2').expand('down').value):
                    if v < 11:
                        station = f'c{n + 2}'
                        sht.range(station).color = (135, 206, 250)
                wb.save()
            print(f'程序运行共花费{round(time.time() - start, 2)}秒，生成一份名字为{self.put_name}的报告')
        except:
            print('文件匹配出错，没有获取到对应格式文件')


if __name__ == "__main__":
    start = time.time()
    c = Re_port()
    c.hand_data()
